package com.xdf.xzymanagementsystem.util;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

@Service
public class CustomSqlUtils {
    @Autowired
    private DataSource dataSource;

    /**
     * 将字符串集合以存储过程+参数的形式拼接为可执行的 SQL
     * @param callProc 字符串集合
     * @return
     */
    public String callProc2SQLString(List<String> callProc){
        if( callProc == null || callProc.size()<=0 ) return "";
        String sqlString = "call " + callProc.get(0) + "(";
        String comma = ",";
        for (int i = 1; i < callProc.size(); i++) {
            if(i == 1)
                comma ="";
            else
                comma =",";

            String currentItem = Optional.ofNullable(callProc.get(i)).orElse("");
            if(currentItem.length()<=0)
                sqlString = sqlString + comma + "''";
            else
                sqlString = sqlString + comma + "'" + callProc.get(i) + "'";
        }

        sqlString += ")";

        return sqlString;
    }

    /**
     * 执行存储过程进行查询
     * @param procAndParams
     * @return
     */
    public List<Map<String,Object>> queryForListCallProc(List<String> procAndParams){
        String sqlString = callProc2SQLString(procAndParams);
        List<Map<String, Object>> mapList = queryForList(sqlString);
        return mapList;
    }

    /**
     * 执行 SQL 字符串进行查询
     * @param sql
     * @return
     */
    public List<Map<String,Object>> queryForList(String sql){
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        List<Map<String,Object>> list = jdbcTemplate.queryForList(sql,new HashMap<>());
        return list;
    }

    /**
     * 执行修改SQL
     * @param sql
     * @return
     */
    public int update(String sql){
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        int ret = jdbcTemplate.update(sql,new HashMap<>());
        return ret;
    }
}
